package dao.mysql;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

import model.LedgerDTO;
import dao.DAOException;
import dao.LedgerDAO;

public class LedgerDAOImpl implements LedgerDAO {
	private static final String dbUrl = "jdbc:mysql://localhost:3306/leaveappforiss";
	private static final String dbUserName = "root";
	private static final String dbPassword = "password";

	// Create oneLedger instance
	public LedgerDTO createoneLedger() {
		return new LedgerDTO();
	}

	public LedgerDTO findLedger(int EmployeeID) throws DAOException {
		String selectSql = "SELECT * FROM Ledger WHERE EmployeeID'"
				+ EmployeeID + "';";
		LedgerDTO oneLedger = loadLedger(selectSql);
		System.out.println(oneLedger.toString());
		return oneLedger;
	}

	private LedgerDTO loadLedger(String selectSql) throws DAOException {
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		LedgerDTO oneLedger = createoneLedger();
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(dbUrl, dbUserName, dbPassword);
			Statement st = conn.createStatement();
			Logger.getLogger(getClass().getName()).log(Level.INFO,
					"Executing select: " + selectSql);
			ResultSet result = st.executeQuery(selectSql);
			while (result.next()) {
				oneLedger.setEmployeeID(result.getInt("EmployeeID"));
				oneLedger.setRemainOverTimeHr(result
						.getDouble("RemainOverTimeHr"));
				oneLedger.setRemainAnnualLeave(result
						.getInt("RemainAnnualLeave"));
				oneLedger.setRemainMedicalLeave(result
						.getInt("RemainMedicalLeave"));
			}
			st.close();
		} catch (Exception e) {
			String error = "Error selecting users. Nested Exception is: " + e;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new DAOException(error);
		} finally {
			try {
				conn.rollback();
				conn.close();
			} catch (Exception e) {
			}
		}
		return oneLedger;
	}

	public ArrayList<LedgerDTO> findAllLedger() throws DAOException {
		String selectSql = "SELECT * FROM Ledger;";
		ArrayList<LedgerDTO> items = loadAllLedger(selectSql);
		// System.out.println(oneLedger.toString());
		return items;
	}

	private ArrayList<LedgerDTO> loadAllLedger(String selectSql)
			throws DAOException {
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		ArrayList<LedgerDTO> items = new ArrayList<LedgerDTO>();
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(dbUrl, dbUserName, dbPassword);
			Statement st = conn.createStatement();
			Logger.getLogger(getClass().getName()).log(Level.INFO,
					"Executing select: " + selectSql);
			ResultSet result = st.executeQuery(selectSql);
			while (result.next()) {
				LedgerDTO oneLedger = createoneLedger();
				oneLedger.setEmployeeID(result.getInt("EmployeeID"));
				oneLedger.setRemainOverTimeHr(result
						.getDouble("RemainOverTimeHr"));
				oneLedger.setRemainAnnualLeave(result
						.getInt("RemainAnnualLeave"));
				oneLedger.setRemainMedicalLeave(result
						.getInt("RemainMedicalLeave"));
				items.add(oneLedger);
			}
			st.close();
		} catch (Exception e) {
			String error = "Error selecting users. Nested Exception is: " + e;
			Logger.getLogger(getClass().getName()).log(Level.SEVERE, error);
			throw new DAOException(error);
		} finally {
			try {
				conn.rollback();
				conn.close();
			} catch (Exception e) {
			}
		}
		return items;
	}

	public void addLedger(LedgerDTO oneLedger) throws SQLException {

		String sql = "";
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet result = null;

		try {
			sql = "INSERT INTO ledger ( EmployeeID, RemainOverTimeHr, "
					+ "RemainAnnualLeave, RemainMedicalLeave "
					+ "VALUES (?, ?, ?, ?);";
			conn = DriverManager.getConnection(dbUrl, dbUserName, dbPassword);
			stmt = conn.prepareStatement(sql);

			stmt.setInt(1, oneLedger.getEmployeeID()); // calculate next id
			stmt.setDouble(2, oneLedger.getRemainOverTimeHr());
			stmt.setInt(3, oneLedger.getRemainAnnualLeave());
			stmt.setInt(4, oneLedger.getRemainMedicalLeave());

			int rowcount = databaseUpdate(conn, stmt);
			if (rowcount != 1) {
				// System.out.println("PrimaryKey Error when updating DB!");
				throw new SQLException("PrimaryKey Error when updating DB!");
			}

		} finally {
			if (stmt != null)
				stmt.close();
		}
	}

	public void updateLedger(LedgerDTO oneLedger) throws DAOException,SQLException {

		String sql = "UPDATE Ledger SET RemainOverTimeHr = ?, RemainAnnualLeave = ?, RemainMedicalLeave = ?"
				+ " WHERE (employeeID = ? ) ";
		PreparedStatement stmt = null;
		Connection conn = DriverManager.getConnection(dbUrl, dbUserName,
				dbPassword);

		try {
			stmt = conn.prepareStatement(sql);

			stmt.setDouble(1, oneLedger.getRemainOverTimeHr());
			stmt.setInt(2, oneLedger.getRemainAnnualLeave());
			stmt.setInt(3, oneLedger.getRemainMedicalLeave());

			stmt.setInt(4, oneLedger.getEmployeeID());

			int rowcount = databaseUpdate(conn, stmt);
			if (rowcount == 0) {
				// System.out.println("Object could not be saved! (PrimaryKey not found)");
				throw new DAOException(
						"Object could not be saved! (PrimaryKey not found)");
			}
			if (rowcount > 1) {
				// System.out.println("PrimaryKey Error when updating DB! (Many objects were affected!)");
				throw new SQLException(
						"PrimaryKey Error when updating DB! (Many objects were affected!)");
			}
		} finally {
			if (stmt != null)
				stmt.close();
		}
	}

	public void deleteLedger(LedgerDTO oneLedger) throws DAOException,SQLException {

		String sql = "DELETE FROM Ledger WHERE (employeeID = ? ) ";
		PreparedStatement stmt = null;

		try {
			Connection conn = DriverManager.getConnection(dbUrl, dbUserName,
					dbPassword);
			stmt = conn.prepareStatement(sql);
			stmt.setInt(1, oneLedger.getEmployeeID());

			int rowcount = databaseUpdate(conn, stmt);
			if (rowcount == 0) {
				// System.out.println("Object could not be deleted (PrimaryKey not found)");
				throw new DAOException(
						"Object could not be deleted! (PrimaryKey not found)");
			}
			if (rowcount > 1) {
				// System.out.println("PrimaryKey Error when updating DB! (Many objects were deleted!)");
				throw new SQLException(
						"PrimaryKey Error when updating DB! (Many objects were deleted!)");
			}
		} finally {
			if (stmt != null)
				stmt.close();
		}
	}

	protected int databaseUpdate(Connection conn, PreparedStatement stmt)
			throws SQLException {

		int result = stmt.executeUpdate();

		return result;
	}
}
